//
//  SQLiteHelperExtend.m
//  Etion
//
//  Created by  user on 11-8-31.
//  Copyright 2011 GuangZhouXuanWu. All rights reserved.
//

#import <Foundation/Foundation.h>

#import "XMSSQLiteHelperExtend.h"
#import "DBColPropertyPair.h"

#import "XMSSelectCol.h"

#import "XMSSelectTable.h"

@implementation CSQLKv

@synthesize SQLkvVarType = _SQLkvVarType;

+ (id)SQLkvWithKey:(NSString *)szKey value:(NSObject *)objvalue type:(ESQLKvVarType)type
{
    CSQLKv *kv = [[CSQLKv new] autorelease_mm];
    kv.key = szKey;
    kv.idValue = objvalue;
    kv.SQLkvVarType = type;
    return kv;
}

- (void)dealloc
{
    superdealloc_mm;
}
@end

@implementation XMSSQLiteHelper (SQLiteHelperExtend)

- (void)putField:(NSArray *)arKv
{
    for (NSUInteger i = 0; i < arKv.count; i++)
    {
        NSUInteger j = i + 1;
        CSQLKv *kv = [arKv objectAtIndex:i];
        if([kv.idValue isKindOfClass:[NSNull class]])
            kv.idValue = nil;
        switch (kv.SQLkvVarType)
        {
            case ESQLKvVarTypeInt:
            {
                if(kv.idValue==nil)
                    [self putFieldNull:j];
                else
                {
                    if([kv.idValue isKindOfClass:[NSNumber class]])
                    {
                        if([NSObject isNSNumberIntType:kv.idValue])
                            [self putFieldInt:j param:[(NSNumber *) kv.idValue integerValue]];
                        else if([NSObject isNSNumberLongLongType:kv.idValue])
                            [self putFieldLongLong:j param:[(NSNumber *) kv.idValue longLongValue]];
                        else if([NSObject isNSNumberBOOLType:kv.idValue])
                            [self putFieldInt:j param:[(NSNumber *) kv.idValue boolValue]];
                    }
                    else if([kv.idValue isKindOfClass:[XMSNumberInt class]])
                        [self putFieldInt:j param:[(XMSNumberInt *) kv.idValue intValue]];
                    else if([kv.idValue isKindOfClass:[XMSNumberLongLong class]])
                        [self putFieldLongLong:j param:[(XMSNumberLongLong *) kv.idValue longLongValue]];
                }
                break;
            }
            case ESQLKvVarTypeDouble:
            {
                if(kv.idValue==nil)
                    [self putFieldNull:j];
                else
                {
                    if([kv.idValue isKindOfClass:[NSNumber class]])
                        [self putFieldDouble:j param:[(NSNumber *) kv.idValue doubleValue]];
                    else if([kv.idValue isKindOfClass:[XMSNumberDouble class]])
                        [self putFieldDouble:j param:[(XMSNumberDouble *) kv.idValue doubleValue]];
                    else if([kv.idValue isKindOfClass:[XMSNumberFloat class]])
                        [self putFieldDouble:j param:[(XMSNumberFloat *) kv.idValue floatValue]];
                }
                break;
            }
            case ESQLKvVarTypeString:
            {
                if(kv.idValue==nil)
                    [self putFieldNull:j];
                else
                    [self putFieldNSString:j param:(NSString *) kv.idValue];
                break;
            }
            case ESQLKvVarTypeGUID:
            {
                if(kv.idValue==nil)
                    [self putFieldNull:j];
                else
                    [self putFieldGUID:j param:((XMSGUID *) kv.idValue).guid];
                break;
            }
            case ESQLKvVarTypeDateTime:
            {
                if(kv.idValue==nil)
                    [self putFieldNull:j];
                else
                    [self putFieldNSDate:j param:(NSDate *) kv.idValue];
                break;
            }
            case ESQLKvVarTypeBytes:
            {
                if(kv.idValue==nil)
                    [self putFieldNull:j];
                else
                    [self putFieldNSData:j param:(NSData *) kv.idValue];
                break;
            }
            case ESQLKvVarTypeArray:
            case ESQLKvVarTypeDictionary:
            {
                if(kv.idValue==nil)
                    [self putFieldNull:j];
                else
                    [self putFieldNSData:j param:(NSData *) [NSKeyedArchiver archivedDataWithRootObject:kv.idValue]];
                break;
            }
            case ESQLKvVarTypeNull:
            {
                [self putFieldNull:j];
                break;
            }
        }
    }
}

- (NSString *)mergeSelectSQL:(NSArray *)arTablename kv:(NSArray *)arKv condition:(NSString *)szCondition
{
    NSMutableString *sql = [[NSMutableString new] autorelease_mm];
    [sql appendString:@"select "];
    if (arKv.count <= 0)
        [sql appendString:@" * "];
    else
    {
        [arKv enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop)
        {
            [sql appendFormat:@"%@,", obj];
        }];
        [sql deleteCharactersInRange:NSMakeRange(sql.length - 1, 1)];
    }
    if (arTablename.count > 0)
    {
        if (arTablename.count == 1)
            [sql appendFormat:@" from %@ ", [arTablename objectAtIndex:0]];
        else
        {
            [sql appendString:@" from "];
            [arTablename enumerateObjectsUsingBlock:^(id obj, NSUInteger idx, BOOL *stop)
            {
                [sql appendFormat:@"%@,", obj];
            }];
            [sql deleteCharactersInRange:NSMakeRange(sql.length - 1, 1)];
        }
    }
    if (szCondition.length > 0)
    {
        [sql appendFormat:@" where %@", szCondition];
    }
    return sql;
}

- (void)prepareSelect:(NSArray *)arTablename kv:(NSArray *)arKv condition:(NSString *)szCondition
{
    NSString *sql = [self mergeSelectSQL:arTablename kv:arKv condition:szCondition];
    [self prepareExecSQL:[sql UTF8String]];
}

- (NSInteger)selectTable:(NSArray *)arTablename kv:(NSArray *)arKv condition:(NSString *)szCondition gettingTable:(void (^)())gettable
{
    return [self selectTable:[[self mergeSelectSQL:arTablename kv:arKv condition:szCondition] UTF8String] gettingTable:gettable];
}

- (NSInteger)selectTable:(NSArray *)arTablename cls:(Class)cls kv:(XMSQueueDictionary *)qdKv condition:(NSString *)szCondition completion:(void (^)(NSArray *arList))completion
{
    NSArray *arKv = nil;
    XMSQueueDictionary *qd = nil;
    if (qdKv.storeCls != [CDBColPropertyPair class])
    {
        arKv=[qdKv allKeys];
        qd = [[[XMSQueueDictionary alloc] initWithStoreClass:[CDBColPropertyPair class]] autorelease_mm];
        for (NSUInteger i = 0; i < [qdKv count]; i++)
        {
            XMSKvPair *p = [qdKv kvPairAtIndex:i];
            ENSObjectPropertyType type = [NSObject NSObjectPropertyType:[NSObject getPropertyType:cls property:p.idValue]];
            CDBColPropertyPair *pair = [[CDBColPropertyPair new] autorelease_mm];
            pair.idKey = p.idKey;
            pair.idValue = p.idValue;
            pair.propertyType = type;
            [qd addKvPairDirect:pair];
        }
    }
    else
    {
        qd = qdKv;
        NSMutableArray *ar = [[NSMutableArray new] autorelease_mm];
        arKv=ar;
        for (CDBColPropertyPair *kv in qdKv)
            [ar addObject:[kv TableAsCol]];
    }

    NSMutableArray *arData = [[NSMutableArray new] autorelease_mm];
    NSInteger nResult = [self selectTable:arTablename kv:arKv condition:szCondition gettingTable:^
    {
        id object = [[cls new] autorelease_mm];
        [arData addObject:object];
        for (NSUInteger i = 0; i < [qd count]; i++)
        {
            id value = nil;
            CDBColPropertyPair *pair = (CDBColPropertyPair *) [qd kvPairAtIndex:i];
            
            NSString *p = (NSString *) pair.idValue;
            switch ((NSInteger) pair.propertyType)
            {
                case ENSObjectPropertyTypeNSString:
                {
                    value = [self getFieldNSString:i];
                    break;
                }
                case ENSObjectPropertyTypeNSNumber:
                {
                    switch ((NSInteger) [NSObject NSNumberType:[object valueForKey:p]])
                    {
                        case ENSObjectPropertyTypeNSNumberInt:
                        {
                            value = [self getFieldNSNumberInt:i];
                            break;
                        }
                    }
                    break;
                }
                case ENSObjectPropertyTypeNumberInt:
                {
                    value = [XMSNumberInt numberIntWithIntValue:[self getFieldInt:i]];
                    break;
                }
                case ENSObjectPropertyTypeNSDate:
                {
                    value = [self getFieldNSDate:i];
                    break;
                }
                case ENSObjectPropertyTypeNSData:
                {
                    value = [self getFieldNSData:i];
                    break;
                }
            }
            [object setValue:value forKey:p];
        }
    }];
    completion(arData.count>0?arData:nil);
    return nResult;
}

- (NSInteger)selectTable:(Class)cls kv:(XMSQueueDictionary *)qdKv SQL:(NSString *)szSQL completion:(void (^)(NSArray *arList))completion
{
    XMSQueueDictionary *qd = nil;
    if (qdKv.storeCls != [CDBColPropertyPair class])
    {
        qd = [[[XMSQueueDictionary alloc] initWithStoreClass:[CDBColPropertyPair class]] autorelease_mm];
        for (NSUInteger i = 0; i < [qdKv count]; i++)
        {
            XMSKvPair *p = [qdKv kvPairAtIndex:i];
            ENSObjectPropertyType type = [NSObject NSObjectPropertyType:[NSObject getPropertyType:cls property:p.idValue]];
            CDBColPropertyPair *pair = [[CDBColPropertyPair new] autorelease_mm];
            pair.idKey = p.idKey;
            pair.idValue = p.idValue;
            pair.propertyType = type;
            [qd addKvPairDirect:pair];
        }
    }
    else
        qd = qdKv;
    NSMutableArray *arData = [[NSMutableArray new] autorelease_mm];

    XMSQueueDictionary* qdIndex=[[XMSQueueDictionary new]autorelease_mm];
    __block BOOL bFind=NO;
    
    NSInteger nResult = [self selectTable:[szSQL UTF8String] gettingTable:^(BOOL *bStop)
    {
        if(bFind==NO)
        {
            bFind=YES;
            
            NSUInteger colCount = [self getColumnCount];
            for(NSUInteger i = 0; i< colCount ;i++)
            {
                NSString* szColname = [self getColumnName:i];
                for(NSUInteger j = 0;j < [qd count];j++)
                {
                    XMSKvPair* p = (XMSKvPair* )[qd kvPairAtIndex:j];
                    if([p isMemberOfClass:[CDBColPropertyPair class]])
                    {
                        CDBColPropertyPair *pair = (CDBColPropertyPair *)p;
                        if([pair.szCol isEqualToString:szColname] || [pair.szAscol isEqualToString:szColname])
                        {
                            [qdIndex addKey:[NSNumber numberWithUnsignedInteger:i] value:[NSNumber numberWithUnsignedInteger:j]];
                            break;
                        }
                    }
                    else if([p isMemberOfClass:[XMSModelTransformPair class]])
                    {
                        XMSModelTransformPair *pair = (XMSModelTransformPair *)p;
                        if([pair.key isEqualToString:szColname])
                        {
                            [qdIndex addKey:[NSNumber numberWithUnsignedInteger:i] value:[NSNumber numberWithUnsignedInteger:j]];
                            break;
                        }
                    }
                }
            }
        }
        
        id object = [[cls new] autorelease_mm];
        [arData addObject:object];
        for (NSUInteger i = 0; i < [qdIndex count]; i++)
        {
            XMSKvPair* col=[qdIndex kvPairAtIndex:i];
            id value = nil;
            CDBColPropertyPair *pair = (CDBColPropertyPair *) [qd kvPairAtIndex:[col.idValue integerValue]];
            NSString *p = (NSString *) pair.idValue;
            NSUInteger colIndex = [col.idKey unsignedIntegerValue];
            switch ((NSInteger) pair.propertyType)
            {
                case ENSObjectPropertyTypeNSString:
                {
                    value = [self getFieldNSString:colIndex];
                    break;
                }
                case ENSObjectPropertyTypeNSNumber:
                {
                    NSInteger type = [self getColumnType:colIndex];
                    if(type == SQLITE_TEXT ||type == SQLITE_NULL)
                    {
                        value = nil;
                    }
                    else
                    {
                        switch ((NSInteger) [NSObject NSNumberType:[object valueForKey:p]])
                        {
                            case ENSObjectPropertyTypeNSNumberInt:
                            {
                                value = [self getFieldNSNumberInt:colIndex];
                                break;
                            }
                            case ENSObjectPropertyTypeNSNumberLongLong:
                            {
                                value = [self getFieldNSNumberLongLong:colIndex];
                                break;
                            }
                        }
                    }
                    break;
                }
                case ENSObjectPropertyTypeNumberInt:
                {
                    NSInteger type = [self getColumnType:colIndex];
                    if(type == SQLITE_TEXT ||type == SQLITE_NULL)
                        value = nil;
                    else
                        value = [XMSNumberInt numberIntWithIntValue:[self getFieldInt:colIndex]];
                    break;
                }
                case ENSObjectPropertyTypeNumberLongLong:
                {
                    NSInteger type = [self getColumnType:colIndex];
                    if(type == SQLITE_TEXT ||type == SQLITE_NULL)
                        value = nil;
                    else
                        value = [XMSNumberLongLong numberLongLongWithLongLongValue:[self getFieldLongLong:colIndex]];
                    break;
                }
                case ENSObjectPropertyTypeNumberFloat:
                {
                    NSInteger type = [self getColumnType:colIndex];
                    if(type == SQLITE_TEXT ||type == SQLITE_NULL)
                        value = nil;
                    else
                        value = [XMSNumberFloat numberFloatWithFloatValue:[self getFieldDouble:colIndex]];
                    break;
                }
                case ENSObjectPropertyTypeNumberDouble:
                {
                    NSInteger type = [self getColumnType:colIndex];
                    if(type == SQLITE_TEXT ||type == SQLITE_NULL)
                        value = nil;
                    else
                        value = [XMSNumberDouble numberDoubleWithDoubleValue:[self getFieldDouble:colIndex]];
                    break;
                }
                case ENSObjectPropertyTypeNSDate:
                {
                    value = [self getFieldNSDate:colIndex];
                    break;
                }
                case ENSObjectPropertyTypeNSData:
                {
                    value = [self getFieldNSData:colIndex];
                    break;
                }
                case ENSObjectPropertyTypeArray:
                {
                    NSData * data = [self getFieldNSData:colIndex];
                    if(data.length > 0 )
                        value = [NSKeyedUnarchiver unarchiveObjectWithData:data];
                    break;
                }
                case ENSObjectPropertyTypeDictionary:
                {
                    NSData * data = [self getFieldNSData:colIndex];
                    if(data.length > 0 )
                        value = [NSKeyedUnarchiver unarchiveObjectWithData:data];
                    break;
                }
            }
            [object setValue:value forKey:p];
        }
    }];
    completion(arData.count>0?arData:nil);
    return nResult;
}

- (NSString *)mergeInsertSQL:(NSString *)szTablename kv:(NSArray *)arKv ignoreTag:(BOOL)bTag
{
    NSMutableString *sql = [[NSMutableString new] autorelease_mm];
    if (bTag == NO)
        [sql appendFormat:@"insert into %@(", szTablename];
    else
        [sql appendFormat:@"insert or ignore into %@(", szTablename];
    for (CSQLKv *key in arKv)
    {
        [sql appendFormat:@"%@,", key.key];
    }
    [sql deleteCharactersInRange:NSMakeRange(sql.length - 1, 1)];
    [sql appendString:@")values("];
    for (NSUInteger i = 0; i < arKv.count; i++)
    {
        [sql appendString:@"?,"];
    }
    [sql deleteCharactersInRange:NSMakeRange(sql.length - 1, 1)];
    [sql appendString:@")"];
    return sql;
}

- (NSString *)mergeInsertSQL:(NSString *)szTablename kv:(NSArray *)arKv
{
    return [self mergeInsertSQL:szTablename kv:arKv ignoreTag:NO];
}

- (void)prepareInsert:(NSString *)szTablename kv:(NSArray *)arKv
{
    NSString *sql = [self mergeInsertSQL:szTablename kv:arKv];
    [self prepareExecSQL:[sql UTF8String]];
    [self putField:arKv];
}

- (NSInteger)runInsert:(NSString *)szTablename kv:(NSArray *)arKv
{
    [self prepareInsert:szTablename kv:arKv];
    return [self runSQL];
}

- (NSString *)mergeInsertOrIgnoreSQL:(NSString *)szTablename kv:(NSArray *)arKv
{
    return [self mergeInsertSQL:szTablename kv:arKv ignoreTag:YES];
}

- (void)prepareInsertOrIgnore:(NSString *)szTablename kv:(NSArray *)arKv
{
    NSString *sql = [self mergeInsertOrIgnoreSQL:szTablename kv:arKv];
    [self prepareExecSQL:[sql UTF8String]];
    [self putField:arKv];
}

- (NSInteger)runInsertOrIgnore:(NSString *)szTablename kv:(NSArray *)arKv
{
    [self prepareInsertOrIgnore:szTablename kv:arKv];
    return [self runSQL];
}

- (NSString *)mergeUpdateSQL:(NSString *)szTablename valueKv:(NSArray *)arKv1 conditionKv:(NSArray *)arKv2
{
    NSMutableString *sql = [[NSMutableString new] autorelease_mm];
    [sql appendFormat:@"update %@ set ", szTablename];
    for (CSQLKv *k in arKv1)
    {
        [sql appendFormat:@"%@=?,", k.key];
    }
    [sql deleteCharactersInRange:NSMakeRange(sql.length - 1, 1)];
    [sql appendFormat:@" where "];
    for (CSQLKv *k in arKv2)
    {
        [sql appendFormat:@"%@=? and ", k.key];
    }
    [sql deleteCharactersInRange:NSMakeRange(sql.length - 5, 5)];
    return sql;
}

- (void)prepareUpdate:(NSString *)szTablename valueKv:(NSArray *)arKv1 conditionKv:(NSArray *)arKv2
{
    NSString *sql = [self mergeUpdateSQL:szTablename valueKv:arKv1 conditionKv:arKv2];
    [self prepareExecSQL:[sql UTF8String]];
    NSMutableArray *arKv = [[[NSMutableArray alloc] initWithCapacity:arKv1.count + arKv2.count] autorelease_mm];
    [arKv addObjectsFromArray:arKv1];
    [arKv addObjectsFromArray:arKv2];
    [self putField:arKv];
}

- (NSInteger)runUpdate:(NSString *)szTablename valueKv:(NSArray *)arKv1 conditionKv:(NSArray *)arKv2
{
    [self prepareUpdate:szTablename valueKv:arKv1 conditionKv:arKv2];
    return [self runSQL];
}

- (NSString *)mergeDeleteSQL:(NSString *)szTablename kv:(NSArray *)arKv
{
    NSMutableString *sql = [[NSMutableString new] autorelease_mm];
    if(arKv.count>0)
    {
        [sql appendFormat:@"delete from %@ where ", szTablename];
        for (CSQLKv *k in arKv)
        {
            [sql appendFormat:@"%@=? and ", k.key];
        }
        [sql deleteCharactersInRange:NSMakeRange(sql.length - 5, 5)];
    }
    else
        [sql appendFormat:@"delete from %@", szTablename];
    return sql;
}

- (void)prepareDelete:(NSString *)szTablename kv:(NSArray *)arKv
{
    NSString *sql = [self mergeDeleteSQL:szTablename kv:arKv];
    [self prepareExecSQL:[sql UTF8String]];
    [self putField:arKv];
}

- (NSInteger)runDelete:(NSString *)szTablename kv:(NSArray *)arKv
{
    [self prepareDelete:szTablename kv:arKv];
    return [self runSQL];
}

-(NSInteger)runDelete:(NSString *)szTablename
{
    return [self runDelete:szTablename kv:nil];
}

- (NSInteger)runDelete:(NSString *)szTablename condition:(NSString*)condition
{
    return [self immediateRunSQL:[[NSString stringWithFormat:@"delete from %@ where %@",szTablename,condition]UTF8String]];
}

- (NSString *)mergeRelpace:(NSString *)szTalbename kv:(NSArray *)arkv
{
    NSMutableString *s = [[NSMutableString new] autorelease_mm];
    [s appendFormat:@"replace into %@ (", szTalbename];
    for (CSQLKv *kv in arkv)
    {
        [s appendFormat:@"%@,", kv.key];
    }
    [s deleteCharactersInRange:NSMakeRange(s.length - 1, 1)];
    [s appendString:@") values ("];
    for (CSQLKv *kv in arkv)
    {
        switch (kv.SQLkvVarType)
        {
            case ESQLKvVarTypeInt:
            {
                if(kv.idValue == nil)
                    [s appendString:@""];
                else
                [s appendString:[(NSNumber *) kv.idValue stringValue]];
                break;
            }
            case ESQLKvVarTypeDouble:
            {
                if(kv.idValue == nil)
                    [s appendString:@""];
                else
                [s appendString:[(NSNumber *) kv.idValue stringValue]];
                break;
            }
            case ESQLKvVarTypeString:
            {
                if (((NSString *) kv.idValue).length > 0)
                {
                    [s appendString:@"'"];
                    [s appendString:(NSString *) kv.idValue];
                    [s appendString:@"'"];
                }
                else
                    [s appendString:@"''"];
                break;
            }
            case ESQLKvVarTypeGUID:
            {
                break;
            }
            case ESQLKvVarTypeDateTime:
            {
                if(kv.idValue == nil)
                    [s appendString:@""];
                else
                    [s appendString:[[NSNumber numberWithDouble:[(NSDate *) kv.idValue timeIntervalSince1970]] stringValue]];
                break;
            }
            case ESQLKvVarTypeBytes:
            {
                break;
            }
            case ESQLKvVarTypeArray:
            {
                break;
            }
            case ESQLKvVarTypeDictionary:
            {
                break;
            }
            case ESQLKvVarTypeNull:
            {
                break;
            }
        }
        [s appendString:@","];
    }
    [s deleteCharactersInRange:NSMakeRange(s.length - 1, 1)];
    [s appendString:@")"];
    return s;
}

- (void)prepareReplace:(NSString *)szTablename kv:(NSArray *)arKv
{
    NSString *sql = [self mergeRelpace:szTablename kv:arKv];
    [self prepareExecSQL:[sql UTF8String]];
}

- (NSInteger)runReplace:(NSString *)szTablename kv:(NSArray *)arKv
{
    [self prepareReplace:szTablename kv:arKv];
    return [self runSQL];
}

//- (void)PrepareUpdateOrInsertIgnore:(NSString *)szTablename value:(NSArray *)arValue condition:(NSArray *)arCondition
//{
//    [self prepareUpdate:szTablename valueKv:arValue conditionKv:arCondition];
//    [self prepareInsertOrIgnore:szTablename kv:arValue];
//}

- (NSInteger)runUpdateOrInsertIgnore:(NSString *)szTablename valueKv:(NSArray *)arValue conditionKv:(NSArray *)arCondition
{
    [self prepareUpdate:szTablename valueKv:arValue conditionKv:arCondition];
    NSInteger nResult = [self runSQL];
    [self prepareInsertOrIgnore:szTablename kv:arValue];
    nResult=[self runSQL];
    return nResult;
}

- (BOOL)table:(NSString*)tableName containDataWithCondition:(NSString*)condition
{
    NSString *sqlString = [NSString stringWithFormat:@"select count(1) from %@ where %@",tableName,condition];
    
    __block BOOL exist = NO;
    [self selectTable:[sqlString UTF8String] gettingTable:^(BOOL *bStop)
    {
        NSInteger i = [self getFieldInt:0];
        exist = i > 0 ? YES : NO;
    }];
    return exist;
}

- (NSArray *)mergeSQLKv:(XMSQueueDictionary *)qdTableheadkv SQLKv:(XMSQueueDictionary *)qdSqlkv
{
    NSMutableArray *ar = [[NSMutableArray new] autorelease_mm];
    for (NSUInteger i = 0; i < [qdSqlkv count]; i++)
    {
        XMSKvPair *sqlkv = [qdSqlkv kvPairAtIndex:i];
        for (NSUInteger j = 0; j < [qdTableheadkv count]; j++)
        {
            XMSKvPair *tableheadkv = [qdTableheadkv kvPairAtIndex:j];
            XMSStringKv *tableheadkvstr = [XMSStringKv stringKvWithKey:tableheadkv.idKey value:tableheadkv.idValue];
            if ([(NSString *) sqlkv.idKey isEqualToString:tableheadkvstr.key] == YES)
            {
                ESQLKvVarType type = ESQLKvVarTypeString;

                if ([tableheadkvstr.value rangeOfString:@"int" options:NSCaseInsensitiveSearch].location != NSNotFound)
                {
                    type = ESQLKvVarTypeInt;
                }
                else if ([tableheadkvstr.value rangeOfString:@"text" options:NSCaseInsensitiveSearch].location != NSNotFound)
                {
                    type = ESQLKvVarTypeString;
                }
                else if ([tableheadkvstr.value rangeOfString:@"double" options:NSCaseInsensitiveSearch].location != NSNotFound)
                {
                    type = ESQLKvVarTypeDouble;
                }
                else if ([tableheadkvstr.value rangeOfString:@"float" options:NSCaseInsensitiveSearch].location != NSNotFound)
                {
                    type = ESQLKvVarTypeDouble;
                }
                else if ([tableheadkvstr.value rangeOfString:@"datetime" options:NSCaseInsensitiveSearch].location != NSNotFound)
                {
                    type = ESQLKvVarTypeDateTime;
                }

                CSQLKv *sqlkv = [CSQLKv SQLkvWithKey:sqlkv.idKey value:sqlkv.idValue type:type];
                [ar addObject:sqlkv];

                break;
            }
        }
    }
    return ar;
}

- (NSArray *)mergeSQLKv:(XMSQueueDictionary *)qdSqlkv
{
    NSInteger nType = 0;
    if (qdSqlkv.storeCls == [CDBColPropertyPair class])
        nType = 1;
    NSMutableArray *ar = [[NSMutableArray new] autorelease_mm];
    for (NSUInteger i = 0; i < [qdSqlkv count]; i++)
    {
        XMSKvPair *sqlkv = [qdSqlkv kvPairAtIndex:i];

        ESQLKvVarType type = ESQLKvVarTypeString;
        id key = sqlkv.idKey;
        id value = sqlkv.idValue;
        if (nType == 1)
        {
            CDBColPropertyPair *dbcolpair = (CDBColPropertyPair *) sqlkv;
            switch ((NSInteger) dbcolpair.propertyType)
            {
                case ENSObjectPropertyTypeNSString:
                {
                    type = ESQLKvVarTypeString;
                    break;
                }
                case ENSObjectPropertyTypeNSDate:
                {
                    type = ESQLKvVarTypeDateTime;
                    break;
                }
                case ENSObjectPropertyTypeNSData:
                {
                    type = ESQLKvVarTypeBytes;
                    break;
                }
                case ENSObjectPropertyTypeNumberInt:
                case ENSObjectPropertyTypeNSNumberInt:
                case ENSObjectPropertyTypeNumberLongLong:
                {
                    type = ESQLKvVarTypeInt;
                    break;
                }
                case ENSObjectPropertyTypeNumberFloat:
                {
                    value = ((XMSNumber *) value).toNSNumber;
                    type = ESQLKvVarTypeDouble;
                    break;
                }
                case ENSObjectPropertyTypeNSNumberFloat:
                {
                    type = ESQLKvVarTypeDouble;
                    break;
                }
                case ENSObjectPropertyTypeNumberDouble:
                {
                    value = ((XMSNumber *) value).toNSNumber;
                    type = ESQLKvVarTypeDouble;
                    break;
                }
                case ENSObjectPropertyTypeNSNumberDouble:
                {
                    type = ESQLKvVarTypeDouble;
                    break;
                }
                case ENSObjectPropertyTypeArray:
                case ENSObjectPropertyTypeMutableArray:
                {
                    type = ESQLKvVarTypeArray;
                    break;
                }
                case ENSObjectPropertyTypeDictionary:
                case ENSObjectPropertyTypeMutableDictionary:
                {
                    type = ESQLKvVarTypeDictionary;
                    break;
                }
            }
        }
        else
        {
            if ([sqlkv.idValue isKindOfClass:[NSString class]] == YES)
            {
                type = ESQLKvVarTypeString;
            }
            else if ([sqlkv.idValue isKindOfClass:[NSDate class]] == YES)
            {
                type = ESQLKvVarTypeDateTime;
            }
            else if ([sqlkv.idValue isKindOfClass:[NSData class]] == YES)
            {
                type = ESQLKvVarTypeBytes;
            }
            else if ([sqlkv.idValue isKindOfClass:[NSNumber class]] == YES)
            {
                NSNumber *num = (NSNumber *) sqlkv.idValue;
                if ([NSObject isNSNumberIntType:num] == YES || [NSObject isNSNumberLongLongType:num] == YES)
                {
                    type = ESQLKvVarTypeInt;
                }
                else if ([NSObject isNSNumberFloatType:num] == YES)
                {
                    type = ESQLKvVarTypeDouble;
                }
                else if ([NSObject isNSNumberDoubleType:num] == YES)
                {
                    type = ESQLKvVarTypeDouble;
                }
            }
            else if ([sqlkv.idValue isMemberOfClass:[XMSNumberInt class]] == YES)
            {
                value = ((XMSNumber *) value).toNSNumber;
                type = ESQLKvVarTypeInt;
            }
            else if ([sqlkv.idValue isMemberOfClass:[XMSNumberLongLong class]] == YES)
            {
                value = ((XMSNumber *) value).toNSNumber;
                type = ESQLKvVarTypeInt;
            }
            else if ([sqlkv.idValue isMemberOfClass:[XMSNumberFloat class]] == YES)
            {
                value = ((XMSNumber *) value).toNSNumber;
                type = ESQLKvVarTypeDouble;
            }
            else if ([sqlkv.idValue isMemberOfClass:[XMSNumberDouble class]] == YES)
            {
                value = ((XMSNumber *) value).toNSNumber;
                type = ESQLKvVarTypeDouble;
            }
            else if ([sqlkv.idValue isKindOfClass:[NSArray class]] == YES)
            {
                type = ESQLKvVarTypeArray;
            }
            else if ([sqlkv.idValue isKindOfClass:[NSDictionary class]] == YES)
            {
                type = ESQLKvVarTypeDictionary;
            }
        }

        CSQLKv *kv = [CSQLKv SQLkvWithKey:key value:value type:type];
        [ar addObject:kv];

    }
    return ar;
}

- (XMSQueueDictionary*)parseSelectSQL:(NSString*)sql
{
    NSString *tmp=@"";
    
    NSString *selectParten = @"(\\s)*(select )(\\s)*(distinct )(\\s)*|(\\s)*(select )(\\s)*";
    NSRegularExpression *selectExpression = [NSRegularExpression regularExpressionWithPattern:selectParten options:nil error:nil];
    NSArray* selectMatch = [selectExpression matchesInString:sql options:NSMatchingReportCompletion range:NSMakeRange(0, [sql length])];
    if(selectMatch.count<=0)
        return NO;
    NSTextCheckingResult *selectResult = [selectMatch objectAtIndex:0];
    if(selectResult.range.location != 0)
        return NO;
    
    tmp = [sql substringFromIndex:selectResult.range.location+selectResult.range.length];
    
    NSString *fromParten = @"(\\s)*(from )(\\s)*";
    NSRegularExpression *fromExpression = [NSRegularExpression regularExpressionWithPattern:fromParten options:nil error:nil];
    NSArray* fromMatch = [fromExpression matchesInString:tmp options:NSMatchingReportCompletion range:NSMakeRange(0, [tmp length])];
    if(fromMatch.count<=0)
        return NO;
    NSTextCheckingResult *fromResult = [fromMatch firstObject];
    
    NSString *selectCol = [tmp substringToIndex:fromResult.range.location];
    
    tmp = [tmp substringFromIndex:fromResult.range.location + fromResult.range.length];
    
    NSMutableArray *arSelectCol = [[NSMutableArray new] autorelease_mm];
    if([selectCol isEqualToString:@"*"])
    {
        [arSelectCol addObject:[XMSSelectCol selectColWithColName:@"*"]];
    }
    else
    {
        NSArray *ar = [selectCol componentsSeparatedByString:@","];
        for(NSString *s in ar)
        {
            NSString *tableName = @"";
            NSString *asName = @"";
            NSString *colName = [s stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
            
            //A.a as b
            NSString *asParten = @"(\\s)*( as )(\\s)*";
            NSRegularExpression *asExpression = [NSRegularExpression regularExpressionWithPattern:asParten options:nil error:nil];
            NSArray* asMatch = [asExpression matchesInString:s options:NSMatchingReportCompletion range:NSMakeRange(0, [s length])];
            if(asMatch.count>0)
            {
                NSTextCheckingResult *asResult = [asMatch lastObject];
                asName = [s substringFromIndex:asResult.range.location + asResult.range.length];
                
                s = [s substringToIndex:asResult.range.location];
            }

            //A.a
            NSString *pointParten = @"(\\s)*(\\.)(\\s)*";
            NSRegularExpression *pointExpression = [NSRegularExpression regularExpressionWithPattern:pointParten options:nil error:nil];
            NSArray* pointMatch = [pointExpression matchesInString:s options:NSMatchingReportCompletion range:NSMakeRange(0, [s length])];
            if(pointMatch.count>0)
            {
                NSTextCheckingResult *pointResult = [pointMatch lastObject];
                NSString *tmp = [s substringFromIndex:pointResult.range.location + pointResult.range.length];
                
                if([tmp isEqualToString:@"*"] == YES)
                {
                    tableName = [s substringToIndex:pointResult.range.location];
                    
                    colName = @"*";
                }
                else
                {
                    tableName = [s substringToIndex:pointResult.range.location];
                    
                    colName = tmp;
                }
            }
            else
            {
                colName = s;
            }

            //a b
            NSString *whitespaceParten = @"(\\s)*( )(\\s)*";
            NSRegularExpression *whitespaceExpression = [NSRegularExpression regularExpressionWithPattern:whitespaceParten options:nil error:nil];
            NSArray* whitespaceMatch = [whitespaceExpression matchesInString:colName options:NSMatchingReportCompletion range:NSMakeRange(0, [colName length])];
            if(whitespaceMatch.count>0)
            {
                NSTextCheckingResult *whitespaceResult = [whitespaceMatch lastObject];
                NSString *tmp = [colName substringFromIndex:whitespaceResult.range.location + whitespaceResult.range.length];
                
                asName = tmp;
                colName = [colName substringToIndex:whitespaceResult.range.location];
                
            }
            
            tableName = [tableName stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
            
            [arSelectCol addObject:[XMSSelectCol selectColWithTableName:tableName colName:colName asName:asName]];
        }
    }
    
    NSMutableArray *arTable = [[NSMutableArray new] autorelease_mm];

    NSString *whereParten = @"(\\s)*( where )(\\s)*";
    NSRegularExpression *whereExpression = [NSRegularExpression regularExpressionWithPattern:whereParten options:nil error:nil];
    NSArray* whereMatch = [whereExpression matchesInString:tmp options:NSMatchingReportCompletion range:NSMakeRange(0, [tmp length])];
    if(whereMatch.count >0 )
    {
        NSTextCheckingResult *result = [whereMatch firstObject];
        NSString *tableName = [tmp substringToIndex:result.range.location];
        
        [arTable addObjectsFromArray:[self parseSelectTable:tableName]];
    }
    
    if(arTable.count <= 0)
    {
        NSString *orderbyParten = @"(\\s)*(order )(\\s)*(by )(\\s)*";
        NSRegularExpression *orderbyExpression = [NSRegularExpression regularExpressionWithPattern:orderbyParten options:nil error:nil];
        NSArray* orderbyMatch = [orderbyExpression matchesInString:tmp options:NSMatchingReportCompletion range:NSMakeRange(0, [tmp length])];
        if(orderbyMatch.count >0 )
        {
            NSTextCheckingResult *result = [orderbyMatch lastObject];
            NSString *tableName = [tmp substringToIndex:result.range.location];
            [arTable addObjectsFromArray:[self parseSelectTable:tableName]];
        }
    }
    
    if(arTable.count <= 0)
    {
        [arTable addObjectsFromArray:[self parseSelectTable:tmp]];
    }
    
    if(arTable.count <= 0)
    {
        NSString *t = [tmp stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        
        XMSSelectTable *st = [self parseSelectTableAs:t];
        
        [arTable addObject:st];
    }
    
    XMSQueueDictionary *qd = [XMSQueueDictionary new];
    if(arTable.count > 0)
    {
        for(XMSSelectTable *st in arTable)
        {
            NSString *table = st.asName.length > 0 ? st.asName : st.tablbeName;
            
            NSMutableArray *ar = [qd getValueForKey:st compareKey:^BOOL(id key)
            {
                XMSSelectTable *selT = key;
                NSString *t = selT.asName.length > 0 ? selT.asName : selT.tablbeName;
                if([t isEqualToString:table])
                    return YES;
                return NO;
            } value:^id
            {
                return [[NSMutableArray new] autorelease_mm];
            }];
            for(XMSSelectCol *col in arSelectCol)
            {
                if(col.tablbeName.length > 0 && col.asName.length <= 0)
                {
                    if([col.tablbeName isEqualToString:table])
                        [ar addObject:col];
                }
                else
                {
                    [ar addObject:col];
                }
            }
        }
    }
    else if(arTable.count == 1 && arSelectCol.count > 0)
    {
        [qd addKey:arTable[0] value:arSelectCol];
    }
    
    return qd;
}

-(NSArray*)parseSelectTable:(NSString *)tableName
{
    NSMutableArray *arTable = [[NSMutableArray new] autorelease_mm];
    
    NSArray *ar = [tableName componentsSeparatedByString:@","];
    for(NSString *s in ar)
    {
        s = [s stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        
        NSString *leftJoinParten = @"(\\s)*( left )(\\s)*(join )(\\s)*";
        NSRegularExpression *leftJoinExpression = [NSRegularExpression regularExpressionWithPattern:leftJoinParten options:nil error:nil];
        NSArray* leftJoinMatch = [leftJoinExpression matchesInString:s options:NSMatchingReportCompletion range:NSMakeRange(0, [s length])];
        
        if(leftJoinMatch.count > 0)
        {
            NSString *leftJoinStr = s;
            
            NSString *t = [leftJoinStr substringToIndex:((NSTextCheckingResult*)[leftJoinMatch firstObject]).range.location];
            t = [t stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
            
            XMSSelectTable *st = [self parseSelectTableAs:t];
            
            [arTable addObject:st];
            
            for(NSTextCheckingResult *leftJoinResult in leftJoinMatch)
            {
                NSString *t = [leftJoinStr substringFromIndex:leftJoinResult.range.location + leftJoinResult.range.length];
                t = [t substringToIndex:[t rangeOfString:@" on"].location];
                
                XMSSelectTable *st = [self parseSelectTableAs:t];
                
                [arTable addObject:st];
            }
            continue;
        }
        else
        {
            XMSSelectTable *st = [self parseSelectTableAs:s];
            
            [arTable addObject:st];
        }
    }
    return arTable;
}

-(XMSSelectTable*)parseSelectTableAs:(NSString*)s
{
    XMSSelectTable *st = [XMSSelectTable new];
    
    NSString *asParten = @"(\\s)*( as )(\\s)*";
    NSRegularExpression *asExpression = [NSRegularExpression regularExpressionWithPattern:asParten options:nil error:nil];
    NSArray* asMatch = [asExpression matchesInString:s options:NSMatchingReportCompletion range:NSMakeRange(0, s.length)];
    if(asMatch.count == 1)
    {
        NSTextCheckingResult *asResult = [asMatch lastObject];
        
        NSString *t = [s substringFromIndex:asResult.range.location + asResult.range.length];
        t = [t stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        
        st.asName = t;
        
        t = [s substringToIndex:asResult.range.location];
        t = [t stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        st.tablbeName = t;
        
        return st;
    }
    
    NSString *whitespaceParten = @"(\\s)*( )(\\s)*";
    NSRegularExpression *whitespaceExpression = [NSRegularExpression regularExpressionWithPattern:whitespaceParten options:nil error:nil];
    NSArray* whitespaceMatch = [whitespaceExpression matchesInString:s options:NSMatchingReportCompletion range:NSMakeRange(0, s.length)];
    if(whitespaceMatch.count>0)
    {
        NSTextCheckingResult *whitespaceResult = [whitespaceMatch lastObject];
        
        NSString *t = [s substringFromIndex:whitespaceResult.range.location + whitespaceResult.range.length];
        t = [t stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        
        st.asName = t;
        
        t = [s substringToIndex:whitespaceResult.range.location];
        t = [t stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]];
        st.tablbeName = t;
        
        return st;
    }
    
    st.tablbeName = s;
    
    return st;
}

- (NSInteger)runSelectSQL:(NSString*)sql withCls:(Class)cls completion:(void (^)(NSArray *arList))completion
{
    NSInteger nResult = FALSE;
    
    if(cls == [NSMutableDictionary class] || cls == [NSDictionary class])
    {
        NSMutableArray *ar = [[NSMutableArray new] autorelease_mm];
        
        nResult = [self getTable:[sql UTF8String] gettingTable:^(NSUInteger nRow, NSUInteger nCol, NSString *szColname, NSString *szColvalue)
        {
            NSMutableDictionary *dic = nil;
            if(nRow > ar.count || ar.count ==0)
            {
                dic = [[NSMutableDictionary new]autorelease_mm];
                [ar addObject:dic];
            }
            dic = [ar objectAtIndex:nRow];
            [dic setObject:szColvalue forKey:szColname];
        }];
        completion(ar);
    }
    else
    {
        XMSQueueDictionary *qd = [self parseSelectSQL:sql];
        
        if(qd.count <= 0)
            return NO;
        
        NSMutableArray *totalCols = [[NSMutableArray new]autorelease_mm];
        
        for(XMSKvPair *pair in qd)
        {
            XMSSelectTable *selTable = pair.idKey;
            NSArray *selectCols = pair.idValue;
            
            NSString *tableName = selTable.tablbeName;
            
            NSMutableArray *cols = nil;
            //兼容旧代码
            if(selectCols.count == 1 && [((XMSSelectCol*)selectCols[0]).colName isEqualToString:@"*"])
            {
                cols = nil;
            }
            //
            else
            {
                cols = [NSMutableArray new];
                for(XMSSelectCol *col in selectCols)
                {
                    if(col.asName.length > 0)
                    {
                        [cols addObject:col.asName];
                    }
                    else
                    {
                        [cols addObject:col.colName];
                    }
                    
//                    [cols addObject:col.colName];
//                    if(col.asName.length > 0 && [col.asName isEqualToString:col.tablbeName] == NO)
//                        [cols addObject:col.asName];
                }
            }
            
            XMSQueueDictionary *kv = [XMSSQLiteDBTable getDBTableColPropertyPair:tableName DBCol:cols];
            if(kv.count <= 0)
            {
                Method method = class_getClassMethod(cls, @selector(modelIdentificationKey));
                if(method != NULL)
                {
                    if([[cls modelIdentificationKey] isEqualToString:tableName])
                    {
                        if([cols containsObject:@"*"])
                        {
                            [cols removeObject:@"*"];
                            kv = [cls modelTransformForOperationKeys:nil];
                        }
                        XMSQueueDictionary *t = [cls modelTransformForOperationKeys:cols];
                        if(kv != nil)
                            [kv addKvPairFromOtherQueueDictionaryDirect:t];
                        else
                            kv = t;
                    }
                    //反向查找
                }
            }
            if(kv.count <= 0)
            {  
                Method method = class_getClassMethod(cls, @selector(verifyTableName:));
                if(method != NULL)
                {
                    if([cls verifyTableName:tableName])
                    {
                        kv = [cls tableColumnProperty:cols];
                    }
                }
            }
            
            //兼容旧代码
            if(cols.count > 0 && kv.count > 0)
            {
                for(XMSSelectCol *selectCol in selectCols)
                {
                    if(selectCol.asName.length > 0)
                    {
                        XMSKvPair *pair = [kv kvPairAtKey:selectCol.colName];
                        pair.idKey = selectCol.asName;
                    }
                }
            }
            //
            
            [totalCols addObject:kv];
        }
        
        XMSQueueDictionary *qdCol = [[[XMSQueueDictionary alloc]initWithStoreClass:[CDBColPropertyPair class]] autorelease_mm];
        for(XMSQueueDictionary *pair in totalCols)
        {
            [qdCol addKvPairFromOtherQueueDictionary:pair];
        }
        
        nResult = [self selectTable:cls kv:qdCol SQL:sql completion:completion];
    }
    return nResult;
}

- (XMSQueueDictionary *)getTableColsAndType:(NSString *)tableName
{
    XMSQueueDictionary *qd = [self getTableHeads:tableName];
    for(XMSKvPair *pair in qd)
    {
        NSString *strType = pair.idValue;
        if([strType isEqualToString:@"TEXT"])
        {
            pair.idValue = @(ESQLKvVarTypeString);
        }
        else if([strType isEqualToString:@"INTEGER"])
        {
            pair.idValue = @(ESQLKvVarTypeInt);
        }
        else if([strType isEqualToString:@"REAL"])
        {
            pair.idValue = @(ESQLKvVarTypeDouble);
        }
        else
        {
            pair.idValue = @(ESQLKvVarTypeNull);
        }
    }
    return qd;
}

@end
